--    Program: 		CAPPOEXT_POSITION.sql
--    Description:	Extract PO Position information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

DECLARE   
   l_processed_count_1		number;
   l_rejected_count_1 		number;
   l_processed_count_2		number;
   l_rejected_count_2		number;
   l_err_msg				varchar2(2000);
   l_pathname       		varchar2(100);
   l_utl_file_1       		utl_file.file_type;
   l_utl_file_2       		utl_file.file_type;
   l_filename_1       		varchar2(255);
   l_filename_2       		varchar2(255);
   l_ngo_sob				varchar2(20);
   l_set_of_books_id		number;
   
  CURSOR cur_po1(P_SET_OF_BOOKS_NAME varchar2) IS
	   SELECT pap.*
	   FROM   PER_ALL_POSITIONS pap,HR_ORGANIZATION_UNITS hou
	   WHERE  pap.business_group_id = hou.business_group_id 
	   AND    upper(hou.name) = 'BG_'||upper(substr(P_SET_OF_BOOKS_NAME, -4));
		
  CURSOR cur_po2 (P_POSITION_ID number) IS
	   SELECT * 
	   FROM   HR_ALL_POSITIONS_F
       WHERE  POSITION_ID = p_POSITION_ID;
  
    
BEGIN
    
     l_pathname := '/tmp';
     l_filename_1 := 'CAPPO_POSITION_B.dat';
     l_filename_2 := 'CAPPO_POSITION_TL.dat';
     l_utl_file_1 := utl_file.fopen(l_pathname, l_filename_1, 'w');
     l_utl_file_2 := utl_file.fopen(l_pathname, l_filename_2, 'w');
   
     l_processed_count_1 := 0;
     l_rejected_count_1  := 0;
     
     l_processed_count_2 := 0;
     l_rejected_count_2  := 0;
	 
     l_ngo_sob := '&1';	 
   
   BEGIN 
     SELECT set_of_books_id
     INTO   l_set_of_books_id
     FROM   gl_sets_of_books
     WHERE  upper(short_name) = upper(l_ngo_sob);
   EXCEPTION
     WHEN others THEN
       dbms_output.put_line(l_ngo_sob||': set of books name not found');
   END; 
      
    FOR cur IN cur_po1(l_ngo_sob) LOOP    

      utl_file.put_line( l_utl_file_1, 
         cur.POSITION_ID                || chr(9) ||
         cur.BUSINESS_GROUP_ID          || chr(9) ||
         cur.JOB_ID                     || chr(9) ||
         cur.ORGANIZATION_ID            || chr(9) ||
         cur.SUCCESSOR_POSITION_ID      || chr(9) ||
         cur.RELIEF_POSITION_ID         || chr(9) ||
         cur.LOCATION_ID                || chr(9) ||
         cur.POSITION_DEFINITION_ID     || chr(9) ||
         cur.DATE_EFFECTIVE             || chr(9) ||
         cur.COMMENTS                   || chr(9) ||
         cur.DATE_END                   || chr(9) ||
         cur.FREQUENCY                  || chr(9) ||
         cur.NAME                       || chr(9) ||
         cur.PROBATION_PERIOD           || chr(9) ||
         cur.PROBATION_PERIOD_UNITS     || chr(9) ||
         cur.REPLACEMENT_REQUIRED_FLAG  || chr(9) ||
         cur.TIME_NORMAL_FINISH         || chr(9) ||
         cur.TIME_NORMAL_START          || chr(9) ||
         cur.WORKING_HOURS              || chr(9) ||
         cur.REQUEST_ID                 || chr(9) ||
         cur.PROGRAM_APPLICATION_ID     || chr(9) ||
         cur.PROGRAM_ID                 || chr(9) ||
         cur.PROGRAM_UPDATE_DATE        || chr(9) ||
         cur.ATTRIBUTE_CATEGORY         || chr(9) ||
         cur.ATTRIBUTE1                 || chr(9) ||
         cur.ATTRIBUTE2                 || chr(9) ||
         cur.ATTRIBUTE3                 || chr(9) ||
         cur.ATTRIBUTE4                 || chr(9) ||
         cur.ATTRIBUTE5                 || chr(9) ||
         cur.ATTRIBUTE6                 || chr(9) ||
         cur.ATTRIBUTE7                 || chr(9) ||
         cur.ATTRIBUTE8                 || chr(9) ||
         cur.ATTRIBUTE9                 || chr(9) ||
         cur.ATTRIBUTE10                || chr(9) ||
         cur.ATTRIBUTE11                || chr(9) ||
         cur.ATTRIBUTE12                || chr(9) ||
         cur.ATTRIBUTE13                || chr(9) ||
         cur.ATTRIBUTE14                || chr(9) ||
         cur.ATTRIBUTE15                || chr(9) ||
         cur.ATTRIBUTE16                || chr(9) ||
         cur.ATTRIBUTE17                || chr(9) ||
         cur.ATTRIBUTE18                || chr(9) ||
         cur.ATTRIBUTE19                || chr(9) ||
         cur.ATTRIBUTE20                || chr(9) ||
         cur.STATUS                     || chr(9) ||
         cur.OBJECT_VERSION_NUMBER      || chr(9) ||
         cur.LAST_UPDATE_DATE           || chr(9) ||
         cur.LAST_UPDATED_BY            || chr(9) ||
         cur.LAST_UPDATE_LOGIN          || chr(9) ||
         cur.CREATED_BY                 || chr(9) ||
         cur.CREATION_DATE
      );  
	   
      l_processed_count_1 := l_processed_count_1 + 1; 
	  
      FOR cur2 IN cur_po2(cur.POSITION_ID) loop
        
        utl_file.put_line( l_utl_file_2, 
          POSITION_ID                 ||CHR(9)|| 
			EFFECTIVE_START_||CHR(9)||        ||CHR(9)|| 
			EFFECTIVE_END_||CHR(9)||          ||CHR(9)|| 
			AVAILABILITY_STATUS_ID      ||CHR(9)|| 
			BUSINESS_GROUP_ID           ||CHR(9)|| 
			ENTRY_GRADE_ID              ||CHR(9)|| 
			ENTRY_GRADE_RULE_ID         ||CHR(9)|| 
			ENTRY_STEP_ID               ||CHR(9)|| 
			JOB_ID                      ||CHR(9)|| 
			LOCATION_ID                 ||CHR(9)|| 
			ORGANIZATION_ID             ||CHR(9)|| 
			PAY_BASIS_ID                ||CHR(9)|| 
			PAY_FREQ_PAYROLL_ID         ||CHR(9)|| 
			POSITION_DEFINITION_ID      ||CHR(9)|| 
			POSITION_TRANSACTION_ID     ||CHR(9)|| 
			PRIOR_POSITION_ID           ||CHR(9)|| 
			RELIEF_POSITION_ID          ||CHR(9)|| 
			SUCCESSOR_POSITION_ID       ||CHR(9)|| 
			SUPERVISOR_ID               ||CHR(9)|| 
			SUPERVISOR_POSITION_ID      ||CHR(9)|| 
			AMENDMENT_||CHR(9)||              ||CHR(9)|| 
			AMENDMENT_RECOMMENDATION    ||CHR(9)|| 
			AMENDMENT_REF_NUMBER        ||CHR(9)|| 
			AVAIL_STATUS_PROP_END_||CHR(9)||  ||CHR(9)|| 
			BARGAINING_UNIT_CD          ||CHR(9)|| 
			COMMENTS                    ||CHR(9)|| 
			COPIED_TO_OLD_TABLE_FLAG    ||CHR(9)|| 
			CURRENT_JOB_PROP_END_||CHR(9)||   ||CHR(9)|| 
			CURRENT_ORG_PROP_END_||CHR(9)||   ||CHR(9)|| 
			||CHR(9)||_EFFECTIVE              ||CHR(9)|| 
			||CHR(9)||_END                    ||CHR(9)|| 
			EARLIEST_HIRE_||CHR(9)||          ||CHR(9)|| 
			FILL_BY_||CHR(9)||                ||CHR(9)|| 
			FREQUENCY                   ||CHR(9)|| 
			FTE                         ||CHR(9)|| 
			MAX_PERSONS                 ||CHR(9)|| 
			NAME                        ||CHR(9)|| 
			OVERLAP_PERIOD              ||CHR(9)|| 
			OVERLAP_UNIT_CD             ||CHR(9)|| 
			PAY_TERM_END_DAY_CD         ||CHR(9)|| 
			PAY_TERM_END_MONTH_CD       ||CHR(9)|| 
			PERMANENT_TEMPORARY_FLAG    ||CHR(9)|| 
			PERMIT_RECRUITMENT_FLAG     ||CHR(9)|| 
			POSITION_TYPE               ||CHR(9)|| 
			POSTING_DESCRIPTION         ||CHR(9)|| 
			PROBATION_PERIOD            ||CHR(9)|| 
			PROBATION_PERIOD_UNIT_CD    ||CHR(9)|| 
			PROPOSED_FTE_FOR_LAYOFF     ||CHR(9)|| 
			PROPOSED_||CHR(9)||_FOR_LAYOFF    ||CHR(9)|| 
			REPLACEMENT_REQUIRED_FLAG   ||CHR(9)|| 
			REVIEW_FLAG                 ||CHR(9)|| 
			SEASONAL_FLAG               ||CHR(9)|| 
			SECURITY_REQUIREMENTS       ||CHR(9)|| 
			STATUS                      ||CHR(9)|| 
			TERM_START_DAY_CD           ||CHR(9)|| 
			TERM_START_MONTH_CD         ||CHR(9)|| 
			TIME_NORMAL_FINISH          ||CHR(9)|| 
			TIME_NORMAL_START           ||CHR(9)|| 
			UP||CHR(9)||_SOURCE_CD            ||CHR(9)|| 
			WORKING_HOURS               ||CHR(9)|| 
			WORKS_COUNCIL_APPROVAL_FLAG ||CHR(9)|| 
			WORK_PERIOD_TYPE_CD         ||CHR(9)|| 
			WORK_TERM_END_DAY_CD        ||CHR(9)|| 
			WORK_TERM_END_MONTH_CD      ||CHR(9)|| 
			INFORMATION1                ||CHR(9)|| 
			INFORMATION2                ||CHR(9)|| 
			INFORMATION3                ||CHR(9)|| 
			INFORMATION4                ||CHR(9)|| 
			INFORMATION5                ||CHR(9)|| 
			INFORMATION6                ||CHR(9)|| 
			INFORMATION7                ||CHR(9)|| 
			INFORMATION8                ||CHR(9)|| 
			INFORMATION9                ||CHR(9)|| 
			INFORMATION10               ||CHR(9)|| 
			INFORMATION11               ||CHR(9)|| 
			INFORMATION12               ||CHR(9)|| 
			INFORMATION13               ||CHR(9)|| 
			INFORMATION14               ||CHR(9)|| 
			INFORMATION15               ||CHR(9)|| 
			INFORMATION16               ||CHR(9)|| 
			INFORMATION17               ||CHR(9)|| 
			INFORMATION18               ||CHR(9)|| 
			INFORMATION19               ||CHR(9)|| 
			INFORMATION20               ||CHR(9)|| 
			INFORMATION21               ||CHR(9)|| 
			INFORMATION22               ||CHR(9)|| 
			INFORMATION23               ||CHR(9)|| 
			INFORMATION24               ||CHR(9)|| 
			INFORMATION25               ||CHR(9)|| 
			INFORMATION26               ||CHR(9)|| 
			INFORMATION27               ||CHR(9)|| 
			INFORMATION28               ||CHR(9)|| 
			INFORMATION29               ||CHR(9)|| 
			INFORMATION30               ||CHR(9)|| 
			INFORMATION_CATEGORY        ||CHR(9)|| 
			ATTRIBUTE1                  ||CHR(9)|| 
			ATTRIBUTE2                  ||CHR(9)|| 
			ATTRIBUTE3                  ||CHR(9)|| 
			ATTRIBUTE4                  ||CHR(9)|| 
			ATTRIBUTE5                  ||CHR(9)|| 
			ATTRIBUTE6                  ||CHR(9)|| 
			ATTRIBUTE7                  ||CHR(9)|| 
			ATTRIBUTE8                  ||CHR(9)|| 
			ATTRIBUTE9                  ||CHR(9)|| 
			ATTRIBUTE10                 ||CHR(9)|| 
			ATTRIBUTE11                 ||CHR(9)|| 
			ATTRIBUTE12                 ||CHR(9)|| 
			ATTRIBUTE13                 ||CHR(9)|| 
			ATTRIBUTE14                 ||CHR(9)|| 
			ATTRIBUTE15                 ||CHR(9)|| 
			ATTRIBUTE16                 ||CHR(9)|| 
			ATTRIBUTE17                 ||CHR(9)|| 
			ATTRIBUTE18                 ||CHR(9)|| 
			ATTRIBUTE19                 ||CHR(9)|| 
			ATTRIBUTE20                 ||CHR(9)|| 
			ATTRIBUTE21                 ||CHR(9)|| 
			ATTRIBUTE22                 ||CHR(9)|| 
			ATTRIBUTE23                 ||CHR(9)|| 
			ATTRIBUTE24                 ||CHR(9)|| 
			ATTRIBUTE25                 ||CHR(9)|| 
			ATTRIBUTE26                 ||CHR(9)|| 
			ATTRIBUTE27                 ||CHR(9)|| 
			ATTRIBUTE28                 ||CHR(9)|| 
			ATTRIBUTE29                 ||CHR(9)|| 
			ATTRIBUTE30                 ||CHR(9)|| 
			ATTRIBUTE_CATEGORY          ||CHR(9)|| 
			REQUEST_ID                  ||CHR(9)|| 
			PROGRAM_APPLICATION_ID      ||CHR(9)|| 
			PROGRAM_ID                  ||CHR(9)|| 
			PROGRAM_UP||CHR(9)||_||CHR(9)||         ||CHR(9)|| 
			CREATED_BY                  ||CHR(9)|| 
			CREATION_||CHR(9)||               ||CHR(9)|| 
			LAST_UP||CHR(9)||D_BY             ||CHR(9)|| 
			LAST_UP||CHR(9)||_||CHR(9)||            ||CHR(9)|| 
			LAST_UP||CHR(9)||_LOGIN           ||CHR(9)|| 
			OBJECT_VERSION_NUMBER                  
          /*cur2.POSITION_ID|| chr(9) ||
          cur2.LANGUAGE|| chr(9) || 
		  cur2.SOURCE_LANG|| chr(9) || 
		  cur2.NAME|| chr(9) ||
		  cur2.CREATED_BY|| chr(9) || 
		  cur2.CREATION_DATE|| chr(9) ||     
		  cur2.LAST_UPDATED_BY|| chr(9) || 
		  cur2.LAST_UPDATE_DATE|| chr(9) || 
		  cur2.LAST_UPDATE_LOGIN*/
      	);
		l_processed_count_2 := l_processed_count_2 + 1;
      END LOOP;
	      
    END LOOP;
    
    l_err_msg := '';
	IF l_processed_count_1 = 0 THEN
       l_rejected_count_1 := l_rejected_count_1 + 1;
       l_err_msg := l_ngo_sob||': Position not found ';
    END IF;
  
  	INSERT INTO capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  	values ( 'CAPPOEXT_POSITION_B', sysdate, l_processed_count_1, l_rejected_count_1, l_processed_count_1-l_rejected_count_1, l_err_msg );
    
    l_err_msg := '';
    IF l_processed_count_2 = 0 THEN
       l_rejected_count_2 := l_rejected_count_2 + 1;
       l_err_msg := l_ngo_sob||': Position not found';
    END IF;    
      
    INSERT INTO capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ( 'CAPPOEXT_POSITION_TL', sysdate, l_processed_count_2, l_rejected_count_2, l_processed_count_2-l_rejected_count_2, l_err_msg );
         
    COMMIT;
  
    utl_file.fclose(l_utl_file_1);
    utl_file.fclose(l_utl_file_2);
EXCEPTION 
  WHEN others THEN
    dbms_output.put_line(sqlerrm);
    ROLLBACK;
END;
/


--exit
